\section{Modelling of Data Warehouse} \label{sec:modelling}

	Modelling a data warehouse is an iterative and complex process, as data is taken from multiple information sources, then transformed and integrated into a completely different model that is suitable for supporting analytical processing, reporting and decision making. The data in this new model is highly aggregated and helps answering questions that people from business sectors ask, e.g. ``what is the average amount of baggage lost in Aalborg Airport during the days-off''.
	
	\begin{figure*}[h!tb]
		\center
		\includegraphics[width=\textwidth]{parts/images/warehousev2.pdf}
		\caption{Proposed multidimensional data warehouse schema.}
		\label{warehouse}
	\end{figure*}
	
	We found it useful to design a multidimensional model for the warehouse, where the model consists of the central fact table, that is connected with supporting dimensions tables, based on the starflake schema concept.\cite{thebookoftorben} The starflake schema is based on the star schema, but has some parts that use the principles of the snowflake schema, providing less data redundancy at the price of slightly more complex queries in these dimensions. The proposed schema is given in figure \ref{warehouse}.

	As mentioned before, the background of our model is the starflake schema. We chose this type of schema, because it combines advantages from both star and snowflake schemas, i.e. the data in star schema is easy accessible and queryable, while the advantage from the snowflake schema is that the schema is more structured and normalized, not to mention that it reduces data redundancy. 
	
	As it is seen in the proposed schema, all dimension tables have a unique identifier, which is common practice for databases. Also, all dimensions are directly connected to the facts table, except for the subdimensions of \textit{Bag}, as in a snowflake schema. As we denoted in the section of data explanation, it seems logical to avoid the limitation on the number of route legs for a bag. For this purpose, there is a bridge table between \textit{Route} and \textit{RouteLeg}. The \textit{Route} table was introduced to reduce data redundancy by allowing any amount of bags to share a common route.
	
	The information of the timestamps is distributed into two dimensions: \textit{Date} and \textit{Time}. This is done to ensure the possibility of making queries for both of the dimensions simultaneously, i.e. questions like ``how many bags, on average, travel through the sorters in Copenhagen airport in the evening, on weekends?'' If the \textit{Time} and \textit{Date} dimensions were the same, we would not be able to do this.
	Another reason is to reduce repetitions of data. If there would be a record for every second of every day, there would be over 84 thousand records per day, which would have a huge impact on the performance of the data warehouse.

\subsection*{Definitions of dimensions}

	While converting the operational database model into a multidimensional warehouse we extracted the following dimensions:

	\begin{itemize}
		\item Dimension Time;
		\item Dimension Date;
		\item Dimension Location;
		\item Dimension Bag;
		\item Dimension Route (connection between Bag and Route Bridge);
		\item Dimension Route Bridge (bridge between Route and Route Leg);
		\item Dimension Route Leg;
		\item Fact Stay.
	\end{itemize}

	In the following sections these dimensions will be defined in detail.

	\subsubsection*{Dimension Time}

		Dimension table \textit{Time} is for selection and grouping by time. It is defined by three main properties: \textit{t_hour}, \textit{t_minute} and \textit{t_second}. For making the selection more detailed we added an extra property, \textit{t_time_type}. This property defines the time of the day, i.e. morning, afternoon, evening or night. The intervals of these times are not overlapping. By having this property we can ask general questions such as ``what is the average time that bags spend at the sorter in the morning hours?''

	\subsubsection*{Dimension Date}

		Dimension table \textit{Date} is for selection and grouping by date. It is, like \textit{Time}, a rather simple dimension, defined by properties of \textit{d_year}, \textit{d_month} and \textit{d_day}. And also here, we add an extra property, \textit{d_day_type}. This property is for specifying days, i.e. working day and day-off. We are setting saturdays and sundays as a days-off by default and for holidays, we can set this property too for any day of the week.

	\subsubsection*{Dimension Location}

		Dimension table \textit{Location} is more complex. It contains information about country of airport (l_country), name of airport (l_airport), tag reader location (l_tag_reader_location) and location type (l_location_type). The fields \textit{l_country} and \textit{l_airport} are self explanatory. \textit{l_tag_reader_location} defines the indoor location of tag reader, and \textit{l_location_type} tells which type of the reader it is, i.e. \textit{check-in}, \textit{sorter}, \textit{gateway}, etc.

	\subsubsection*{Dimension Bag}

		Dimension table \textit{Bag} contains all unique bags in the system. The model contains information about every individual bag. The bag is identified by the license plate from the source data (b_license_plate) and originating airport (b_originating_airport). It is also associated with a \textit{Route}.

	\subsubsection*{Subdimension Route}

		Dimension table \textit{Route} does not actually contain any data apart from its identifier. It is created to be able to establish a connection between \textit{Bag} and \textit{Route Leg}, via \textit{Route Bridge}. Each \textit{Route Bridge} entry has a foreign key pointing to a \textit{Route}, as well as a \textit{Route Leg}. It serves to simplify searches for bags by route, since it is not even necessary to join the \textit{Bag} table with the \textit{Route} table. The route ID recorded in the \textit{Bag} table suffices in this regard.
		
	\subsubsection*{Subdimension Route Bridge}

		Dimension table \textit{Route Bridge} makes many-to-many connections between \textit{Route} and \textit{Route Leg} possible. Because of this bridge table, a Route can have an unlimited number of route legs. It contains foreign keys of the \textit{Route} and \textit{Route Leg} tables, as well as one extra field, \textit{rb_index}, that defines which leg in a given route sequence this represents. The first airport in a route, after the originating airport, will have index 1, the next will have index 2, etc.

	\subsubsection*{Subdimension Route Leg}

		Dimension table \textit{Route Leg} is used to contain information of one potential leg of a route. It is defined by a destination (rl_destination) and a name of the airline that makes the flight (rl_airlines_name). Each entry in this table can be referenced by any number of routes through \textit{Route Bridge}, such that there are no duplicate route legs.

	\subsubsection*{Fact Stay}

		The fact table, \textit{Stay}, represents a single ``stay'' of a certain bag, in a certain location. It has a foreign key for each dimension, i.e. it is associated with a certain bag in a certain location, at a specific date and time. It contains two measures: the \textit{duration} holds the number of seconds that the bag stayed in its position, and the \textit{status} determines whether the bag is lost at this point.
	
\subsection*{The design of the Cube}

	We designed a multidimensional cube on top of the proposed data warehouse. This section defines the cube by describing measures for analyzing the data warehouse.
	
	\subsubsection*{The measures}
	
	There are only two facts in our fact table -- the duration and the status -- the other fields are just keys to dimensions.
	
	The \textit{duration} fact holds information about how long the bag spent in the active area of some particular RFID reader. From this we can aggregate the following measures:
	
	\begin{itemize}
		\item The minimal duration is the shortest time that bags have spent in a particular location.
		\item The average duration is the average time that bags have spent in a particular location.
		\item The maximum duration is the longest time interval, that bags have spent in a particular location.
	\end{itemize}
	
	The status fact holds information about whether or not the bag is lost at the time of the stay. From this we can aggregate the following measures:
	
	\begin{itemize}
		\item The amount of bags lost in a particular location is determined by summing up the status of distinct bags.
		\item Whether any bags at all are lost on a certain route, or in certain locations, can be determined using a Boolean \textit{or} function.
	\end{itemize}
	
	In addition there are other measures that can be calcultated from the facts table:
	
	\begin{itemize}
		\item The Bags Distinct Count -- the number of distinct bags.
		\item The Location Distinct Count -- the numer of distinct locations.
		\item The Stay Count -- which is the number of stay records in the given dimensional scope.
	\end{itemize}
	
	%\subsubsection*{The fact table}
	
	%The fact table Stay has two measureable properties: status and duration:
	
	%\begin{itemize}
	%	\item Status - the status of bag: ok, lost, unknown;
	%	\item Duration - the period of time spent at the area of specific tag reader.
	%\end{itemize}
	
	%The aggregation functions to be used by the Duration measure are very similar to what would be used in typical data warehouses, e.g. sum (how many bags travelled through gateway 2 at Copenhagen Airport in March 2011) and average (on average, how much time do bags spend on sorters?)
	
	%The Status measure, however, opens up the possibility of aggregating using a Boolean \textit{or} function, letting it be evident if any bags at all on a certain route are lost at any point, or whether any bags were lost on sorter 1 in Aalborg Airport during last weekend. Quantitative questions may also be asked, though, using a sum or average function that treats \textit{lost} as the number 1, and \textit{ok} as the number 0. This allows for very useful questions such as ``how many bags have been lost on the way from Aalborg to Copenhagen'' or ``on average, how many bags are lost by sorter?''